
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/26/2010 22:05:15
-- Generated from EDMX file: D:\Alec\PRJ\freetour\Freetour\Freetour.Domain.Persistence\DAC\AirportServices.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [FinancialManagement];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'PickupOrders'
CREATE TABLE [dbo].[PickupOrders] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Time] datetime  NOT NULL,
    [Timestamp] datetime  NOT NULL,
    [IsMembership] bit  NOT NULL,
    [Memo] nvarchar(max)  NULL,
    [ServiceType_Id] int  NOT NULL,
    [Route_Id] int  NOT NULL,
    [ServiceOrderer_Id] int  NOT NULL
);
GO

-- Creating table 'Airports'
CREATE TABLE [dbo].[Airports] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL,
    [Location_Id] int  NOT NULL
);
GO

-- Creating table 'ServiceTypes'
CREATE TABLE [dbo].[ServiceTypes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL
);
GO

-- Creating table 'Locations'
CREATE TABLE [dbo].[Locations] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Memo] nvarchar(max)  NULL
);
GO

-- Creating table 'ServiceRoutes'
CREATE TABLE [dbo].[ServiceRoutes] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Fee] decimal(18,0)  NOT NULL,
    [Memo] nvarchar(max)  NULL,
    [Airport_Id] int  NOT NULL,
    [Location_Id] int  NOT NULL
);
GO

-- Creating table 'ServiceOrderers'
CREATE TABLE [dbo].[ServiceOrderers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Cellphone] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'PickupOrders'
ALTER TABLE [dbo].[PickupOrders]
ADD CONSTRAINT [PK_PickupOrders]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Airports'
ALTER TABLE [dbo].[Airports]
ADD CONSTRAINT [PK_Airports]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ServiceTypes'
ALTER TABLE [dbo].[ServiceTypes]
ADD CONSTRAINT [PK_ServiceTypes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Locations'
ALTER TABLE [dbo].[Locations]
ADD CONSTRAINT [PK_Locations]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ServiceRoutes'
ALTER TABLE [dbo].[ServiceRoutes]
ADD CONSTRAINT [PK_ServiceRoutes]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'ServiceOrderers'
ALTER TABLE [dbo].[ServiceOrderers]
ADD CONSTRAINT [PK_ServiceOrderers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [ServiceType_Id] in table 'PickupOrders'
ALTER TABLE [dbo].[PickupOrders]
ADD CONSTRAINT [FK_ServiceTypePickupOrder]
    FOREIGN KEY ([ServiceType_Id])
    REFERENCES [dbo].[ServiceTypes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServiceTypePickupOrder'
CREATE INDEX [IX_FK_ServiceTypePickupOrder]
ON [dbo].[PickupOrders]
    ([ServiceType_Id]);
GO

-- Creating foreign key on [Location_Id] in table 'Airports'
ALTER TABLE [dbo].[Airports]
ADD CONSTRAINT [FK_LocationAirport]
    FOREIGN KEY ([Location_Id])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocationAirport'
CREATE INDEX [IX_FK_LocationAirport]
ON [dbo].[Airports]
    ([Location_Id]);
GO

-- Creating foreign key on [Airport_Id] in table 'ServiceRoutes'
ALTER TABLE [dbo].[ServiceRoutes]
ADD CONSTRAINT [FK_AirportRoute]
    FOREIGN KEY ([Airport_Id])
    REFERENCES [dbo].[Airports]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_AirportRoute'
CREATE INDEX [IX_FK_AirportRoute]
ON [dbo].[ServiceRoutes]
    ([Airport_Id]);
GO

-- Creating foreign key on [Location_Id] in table 'ServiceRoutes'
ALTER TABLE [dbo].[ServiceRoutes]
ADD CONSTRAINT [FK_LocationRoute]
    FOREIGN KEY ([Location_Id])
    REFERENCES [dbo].[Locations]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_LocationRoute'
CREATE INDEX [IX_FK_LocationRoute]
ON [dbo].[ServiceRoutes]
    ([Location_Id]);
GO

-- Creating foreign key on [Route_Id] in table 'PickupOrders'
ALTER TABLE [dbo].[PickupOrders]
ADD CONSTRAINT [FK_RoutePickupOrder]
    FOREIGN KEY ([Route_Id])
    REFERENCES [dbo].[ServiceRoutes]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_RoutePickupOrder'
CREATE INDEX [IX_FK_RoutePickupOrder]
ON [dbo].[PickupOrders]
    ([Route_Id]);
GO

-- Creating foreign key on [ServiceOrderer_Id] in table 'PickupOrders'
ALTER TABLE [dbo].[PickupOrders]
ADD CONSTRAINT [FK_ServiceOrdererPickupOrder]
    FOREIGN KEY ([ServiceOrderer_Id])
    REFERENCES [dbo].[ServiceOrderers]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ServiceOrdererPickupOrder'
CREATE INDEX [IX_FK_ServiceOrdererPickupOrder]
ON [dbo].[PickupOrders]
    ([ServiceOrderer_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------